﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.Linq;
using UniMana.Data;
using System.Text;

namespace UniMana.Data
{
    public static class ReportStudentSelectData_Chi
    {
        public static IQueryable GetReportStudentSelectData(this Table<Training_Student_Class> traStudentClass,
                                                            Table<Catalogue_Course> cataCourse,
                                                            Table<Catalogue_Department> cataDep,
                                                            Table<Catalogue_Level> cataLevel,
                                                            Table<Catalogue_Major> cataMaj,
                                                            Table<Catalogue_TrainingType> cataTraType,int _iCou, int _iLevel, int _iDep, int _iMajor, int _iTraType)
        {

                        var view1 = from t in cataMaj
                        join t0 in cataDep on new { Catalogue_DepartmentKey = Convert.ToInt32(t.Catalogue_DepartmentKey) } equals new { Catalogue_DepartmentKey = t0.Catalogue_DepartmentKey }
                        join t1 in traStudentClass on t.Catalogue_MajorKey equals t1.Catalogue_MajorKey
                        join t2 in cataLevel
                              on new { Catalogue_LevelKey = Convert.ToInt32(t1.Catalogue_LevelKey), Column1 = Convert.ToInt32(t.Catalogue_LevelKey) }
                          equals new { Catalogue_LevelKey = t2.Catalogue_LevelKey, Column1 = t2.Catalogue_LevelKey }
                        join t3 in cataCourse on new { Catalogue_CourseKey = Convert.ToInt32(t1.Catalogue_CourseKey) } equals new { Catalogue_CourseKey = t3.Catalogue_CourseKey }
                        join t4 in cataTraType on new { Catalogue_TrainingTypeKey = Convert.ToInt32(t1.Catalogue_TrainingTypeKey) } equals new { Catalogue_TrainingTypeKey = t4.Catalogue_TrainingTypeKey }
                        group new { t3, t0, t2, t, t4 } by new
                        {
                            t3.Catalogue_CourseKey,
                            t3.Name,
                            t0.Catalogue_DepartmentKey,
                            Column1 = t0.Name,
                            t2.Catalogue_LevelKey,
                            Column2 = t2.Name,
                            t.Catalogue_MajorKey,
                            Column3 = t.Name,
                            t4.Catalogue_TrainingTypeKey,
                            Column4 = t4.Name
                        } into g
                        select new
                        {
                            Catalogue_CourseKey = (System.Int32?)g.Key.Catalogue_CourseKey,
                            TenKhoaHoc = g.Key.Name,
                            Catalogue_DepartmentKey = (System.Int32?)g.Key.Catalogue_DepartmentKey,
                            TenKhoa = g.Key.Column1,
                            Catalogue_LevelKey = (System.Int32?)g.Key.Catalogue_LevelKey,
                            TenBDT = g.Key.Column2,
                            Catalogue_MajorKey = (System.Int32?)g.Key.Catalogue_MajorKey,
                            TenNganh = g.Key.Column3,
                            Catalogue_TrainingTypeKey = (System.Int32?)g.Key.Catalogue_TrainingTypeKey,
                            TenLHDT = g.Key.Column4,
                            TongSoSV = (Int64?)g.Count()
                        };
            if (_iDep > 0)
            {
                view1 = view1.Where(p => p.Catalogue_DepartmentKey == int.Parse(_iDep.ToString()));
            }
            if (_iMajor > 0)
            {
                view1 = view1.Where(p => p.Catalogue_MajorKey == int.Parse(_iMajor.ToString()));
            }
            if (_iTraType > 0)
            {
                view1 = view1.Where(p => p.Catalogue_TrainingTypeKey == int.Parse(_iTraType.ToString()));
            }
            if (_iCou > 0)
            {
                view1 = view1.Where(p => p.Catalogue_CourseKey == int.Parse(_iCou.ToString()));
            }
            if (_iLevel > 0)
            {
                view1 = view1.Where(p => p.Catalogue_LevelKey == int.Parse(_iLevel.ToString()));
            }
            return view1.Distinct();

        }
    }
}
